namespace CISLib
{
    using System;
    using System.Data;
    using System.Text;

    public class ManageCustomerCount
    {
        public static DataSet GetCount(Guid areaId, Guid provinceId, Guid catId, int level)
        {
            string strSQL = "";
            StringBuilder sb = new StringBuilder();
            sb.Append("select Tb_region.RegionName,                                                                                      ");
            sb.Append("\t\tTB_CATGroup.CATGroupName,                                                                                    ");
            sb.Append("\t\tTB_CustomerBasicInfo.CustomerLevel,                                                                            ");
            sb.Append("\t\tcount(Tb_region.RegionName) as CustomerCount                                                                    ");
            sb.Append("from  TB_CustomerBasicInfo  left outer join Tb_region on TB_CustomerBasicInfo.regionId = Tb_region.regionId          ");
            sb.Append("\t\t\t\t\t\t\tleft outer join dbo.TB_CATGroup on TB_CustomerBasicInfo.CATId = TB_CATGroup.CATGroupId        ");
            sb.Append("where TB_CustomerBasicInfo.AreaId=@AreaId                                                                        ");
            if (((provinceId.ToString() != "00000000-0000-0000-0000-000000000000") || (catId.ToString() != "00000000-0000-0000-0000-000000000000")) || (level != 0))
            {
                if (((provinceId.ToString() == "00000000-0000-0000-0000-000000000000") && (catId.ToString() == "00000000-0000-0000-0000-000000000000")) && (level != 0))
                {
                    sb.Append("\t\t and TB_CustomerBasicInfo.CustomerLevel=@Level   ");
                }
                else if (((provinceId.ToString() == "00000000-0000-0000-0000-000000000000") && (catId.ToString() != "00000000-0000-0000-0000-000000000000")) && (level == 0))
                {
                    sb.Append("      and TB_CustomerBasicInfo.CATId=@CatId   ");
                }
                else if (((provinceId.ToString() != "00000000-0000-0000-0000-000000000000") && (catId.ToString() == "00000000-0000-0000-0000-000000000000")) && (level == 0))
                {
                    sb.Append("\t\t and TB_CustomerBasicInfo.RegionId=@ProvinceId           ");
                }
                else if (((provinceId.ToString() == "00000000-0000-0000-0000-000000000000") && (catId.ToString() != "00000000-0000-0000-0000-000000000000")) && (level != 0))
                {
                    sb.Append("      and TB_CustomerBasicInfo.CATId=@CatId                  ");
                    sb.Append("\t\t and TB_CustomerBasicInfo.CustomerLevel=@Level   ");
                }
                else if (((provinceId.ToString() != "00000000-0000-0000-0000-000000000000") && (catId.ToString() == "00000000-0000-0000-0000-000000000000")) && (level != 0))
                {
                    sb.Append("\t\t and TB_CustomerBasicInfo.RegionId=@ProvinceId           ");
                    sb.Append("\t\t and TB_CustomerBasicInfo.CustomerLevel=@Level   ");
                }
                else if (((provinceId.ToString() != "00000000-0000-0000-0000-000000000000") && (catId.ToString() != "00000000-0000-0000-0000-000000000000")) && (level == 0))
                {
                    sb.Append("\t\t and TB_CustomerBasicInfo.RegionId=@ProvinceId           ");
                    sb.Append("      and TB_CustomerBasicInfo.CATId=@CatId                  ");
                }
                else
                {
                    sb.Append("      and TB_CustomerBasicInfo.CATId=@CatId                  ");
                    sb.Append("\t\t and TB_CustomerBasicInfo.RegionId=@ProvinceId           ");
                    sb.Append("\t\t and TB_CustomerBasicInfo.CustomerLevel=@Level   ");
                }
            }
            sb.Append("group by Tb_region.RegionName,TB_CATGroup.CATGroupName,TB_CustomerBasicInfo.CustomerLevel   ");
            strSQL = sb.ToString();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(provinceId, "ProvinceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(catId, "CatId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(level, "Level", SqlDbType.Int);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }
    }
}

